package cn.school.listener;

import cn.school.common.datasource.CalciteSqlUtils;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.avatica.util.Quoting;
import org.apache.calcite.config.Lex;
import org.apache.calcite.sql.*;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.util.SqlBasicVisitor;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.jetbrains.annotations.NotNull;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Component
@Slf4j
public class BoundSqlInterceptor implements InnerInterceptor {


    @Override
    public boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
//        log.info("willDoQuery执行sql：{}",boundSql.getSql());
//        String sql = boundSql.getSql();
//        if(handleFilterTable(sql)){
//            String modifiedSql = appendSql(sql ,"tenant_id=1") ;
//            try{
//                MetaObject metaObject = SystemMetaObject.forObject(boundSql);
//                appendWhereSql(sql ,metaObject,boundSql);
//
//            }catch (Exception e){
//                e.printStackTrace();
//            }


//            //对时间格式化做替换
//            modifiedSql = modifiedSql.replaceAll("%Y-%M-%D% H:%I:%S","%Y-%m-%d %H:%i:%s").replaceAll("%Y-%M-%D","%Y-%m-%d");
//            MetaObject metaObject = SystemMetaObject.forObject(boundSql);
//            metaObject.setValue("sql",modifiedSql);
//        }
        return InnerInterceptor.super.willDoQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql);
    }

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {

        InnerInterceptor.super.beforeQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql);
    }

    @Override
    public boolean willDoUpdate(Executor executor, MappedStatement ms, Object parameter) throws SQLException {
        return InnerInterceptor.super.willDoUpdate(executor, ms, parameter);
    }

    @Override
    public void beforeUpdate(Executor executor, MappedStatement ms, Object parameter) throws SQLException {
        InnerInterceptor.super.beforeUpdate(executor, ms, parameter);
    }

    @Override
    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        log.info("beforePrepare SQL ：{}",sh.getBoundSql().getSql());
        InnerInterceptor.super.beforePrepare(sh, connection, transactionTimeout);


    }

    private String appendSql(String sql,MetaObject metaObject,BoundSql boundSql,SqlSelect select, List<Map<String, String>> list,String whereStr) throws SqlParseException {

        System.out.println("whereStr ==========: " + whereStr);
        //添加查询条件
        String selectCondition="tenant_id=1";
        if(!StringUtils.isEmpty(whereStr)){
            for (Map<String, String> stringStringMap : list) {
                Set<String> strings = stringStringMap.keySet();
                Iterator<String> iterator = strings.iterator();
                String alias="";
                while(iterator.hasNext()){
                    alias = iterator.next() +".";
                }
                selectCondition = alias+"tenant_id=1";

            }
            log.error(selectCondition,whereStr);
            //添加查询条件
            SqlNode condition = SqlParser.create(selectCondition).parseExpression();
            SqlNode where = SqlUtil.andExpressions(select.getWhere(),condition);
            if(where.toString().indexOf("`TENANT_ID`")<0){
                ArrayList<SqlNode> list1 = new ArrayList();
                if (select.getWhere().getKind() == SqlKind.AND) {
                    list1.addAll(((SqlCall)select.getWhere()).getOperandList());
                } else {
                    list1.add(select.getWhere());
                }
                list1.add(condition);
                where = SqlStdOperatorTable.AND.createCall(SqlParserPos.QUOTED_ZERO, list1);
            }
            select.setWhere(where);
        }else{
            for (Map<String, String> stringStringMap : list) {
                Set<String> strings = stringStringMap.keySet();
                Iterator<String> iterator = strings.iterator();
                String alias="";
                while(iterator.hasNext()){
                    alias = iterator.next() +".";
                }
                selectCondition = alias+"tenant_id=1";

            }
            log.error(selectCondition,whereStr);
            SqlNode condition = SqlParser.create(selectCondition).parseExpression();
            SqlNode where = SqlUtil.andExpressions(select.getWhere(),condition);
            select.setWhere(where);
        }
        metaObject.setValue("sql", select.toString().replaceAll("`",""));


        return sql;
    }


    private String[] appendWhereSql(String sqlStr,MetaObject metaObject,BoundSql boundSql) throws SqlParseException {
        System.out.println(sqlStr);
        if(sqlStr.indexOf("t_student_info si")>=0){
            log.info("t_sys_user_role AS ur");
        }
        String newSqlStr = sqlStr.replaceAll(" name,","`name`,").
                replaceAll(" value,","`value`,").replaceAll(" status,","`status`,").replaceAll(" \\.status",".`status'");
        SqlParser.Config mysqlConfig = SqlParser.configBuilder()
                .setLex(Lex.MYSQL)
                .setCaseSensitive(false)//大小写敏感
                .setQuoting(Quoting.BACK_TICK)
                .setQuotedCasing(Casing.TO_LOWER)
                .setUnquotedCasing(Casing.TO_LOWER)
                .setConformance(SqlConformanceEnum.MYSQL_5)
                .build();
        SqlNode sqlNode = SqlParser.create(sqlStr, mysqlConfig).parseQuery();
        //获取表的别名
        SqlDialect.DatabaseProduct mysql = SqlDialect.DatabaseProduct.MYSQL;
        List<Map<String, String>> list = CalciteSqlUtils.handlerSqlTableAlias(mysql, newSqlStr);
        final String[] addendSql = {""};
        sqlNode.accept(new SqlBasicVisitor<String>(){
            @SneakyThrows
            @Override
            public String visit(SqlCall call) {
                if (call.getKind().equals(SqlKind.SELECT)) {
                    SqlSelect select = (SqlSelect) call;


                    //获取查询条件
//                    SqlNode sqlNode = SqlParser.create(sql, SqlParser.config().withLex(Lex.MYSQL)).parseQuery();
                    String whereStr ="";
                    if(select.getWhere()!=null){
                         whereStr = select.getWhere().toString();
                    }

                    System.out.println("whereStr:   " + whereStr);
                    appendSql(newSqlStr, metaObject, boundSql, select,  list, whereStr);
                    //获取查询条件
//                    select.getWhere().accept(new SqlBasicVisitor<String>() {
//                        @SneakyThrows
//                        public String visit(SqlCall call) {
//                            if (call.getKind().equals(SqlKind.AND) || call.getKind().equals(SqlKind.OR)) {
//                                SqlBasicCall sql = (SqlBasicCall) call;
//                                SqlBasicCall left = (SqlBasicCall) sql.getOperandList().get(0);
//                                SqlBasicCall right = (SqlBasicCall) sql.getOperandList().get(1);
//                                System.out.println("kind:{}" +sql.getKind());
//                                System.out.println("right:{}"+ right);
////                              appendSql（
//
//
//                            }
//                            return call.getOperator().acceptCall(this, call);
//                        }
//                    });

                }
//                if (call.getKind().equals(SqlKind)) {
//
//                }
                return call.getOperator().acceptCall(this, call);
            }
        });

        return addendSql;
    }
    private String appendSql(String sql,String tenantIDSql){
        String upperSql = sql.toUpperCase();
        //获取sql中的查询条件
        List<String> conditions =  getConditions(sql);
        if(sql.indexOf("t_workflow_student_leave_apply")>0){
            log.info(sql);
        }
        if(conditions.size()>0){
            for (String upperCondition : conditions) {
                //拿到查询条件中的别名
//                String upperCondition = condition.toUpperCase();
                if(upperCondition.indexOf(".")>0){
                    String alias = upperCondition.substring(0,upperCondition.indexOf("."));
                    String appendTenant = alias.replace("(","")+"."+tenantIDSql;
                    //添加租户条件

                    sql = sql.replaceAll("\\?","#").replaceAll("\\(","@L").replaceAll("\\)","@R");
                    upperCondition = upperCondition.replaceAll("\\?","#");
                    String newSql = sql.replaceAll("\\\n"," ");
                    upperCondition = upperCondition.replaceAll("\\(","@L").replaceAll("\\)","@R").replaceAll("\\?","#");
                    String respStr=upperCondition;
                    if(upperCondition.toUpperCase().indexOf("AND")>0){
                        String subAndupperCondition = upperCondition.substring(0,upperCondition.indexOf("AND"));
                        respStr = subAndupperCondition+ " and " + appendTenant +" and ";

                    }

                    if(upperCondition.toUpperCase().indexOf("OR")>0){
                        String subAndupperCondition = upperCondition.substring(0,upperCondition.indexOf("OR"));
                        respStr = subAndupperCondition +" and " + appendTenant +" ";

                    }
                    if(upperCondition.toUpperCase().indexOf("ORDER BY")>0){
                        String subAndupperCondition = upperCondition.substring(0,upperCondition.indexOf("ORDER BY"));
                        respStr = subAndupperCondition +" and " + appendTenant +" ";
                    }
                    if(upperCondition.toUpperCase().indexOf("GROUP BY")>0){
                        String subAndupperCondition = upperCondition.substring(0,upperCondition.indexOf("GROUP BY"));
                        respStr = subAndupperCondition +" and "+ appendTenant +" ";
                    }

                    newSql = newSql.replaceAll(upperCondition,respStr);

                    newSql = newSql.replaceAll("#","?").replaceAll("@L","(").replaceAll("@R",")");
                    sql = newSql;
                }else{
                    sql = sql.replaceAll("\\?","#").replaceAll("\\(","@L").replaceAll("\\)","@R");
                    String newSql = sql.replaceAll("\\\n"," ");
                    upperCondition = upperCondition.replaceAll("\\(","@L").replaceAll("\\)","@R").replaceAll("\\?","#");
                    String respStr=upperCondition;
                    if(upperCondition.toUpperCase().indexOf("AND")>0){
                        String subAndupperCondition = upperCondition.substring(0,upperCondition.indexOf("AND"));
                        respStr = subAndupperCondition+ " and " + tenantIDSql +" and ";

                    }
                    if(upperCondition.toUpperCase().indexOf("ORDER BY")>0){
                        String subAndupperCondition = upperCondition.substring(0,upperCondition.indexOf("ORDER BY"));
                        respStr = subAndupperCondition +" and " + tenantIDSql +" ";
                    }
                    if(upperCondition.toUpperCase().indexOf("GROUP BY")>0){
                        String subAndupperCondition = upperCondition.substring(0,upperCondition.indexOf("GROUP BY"));
                        respStr = subAndupperCondition +" and "+ tenantIDSql +" ";
                    }

                    newSql = newSql.replaceAll(upperCondition,respStr);
                    newSql = newSql.replaceAll("#","?").replaceAll("@L","(").replaceAll("@R",")");



                    sql = newSql;
                }

            }
            return sql;
        }else{
            if (upperSql.indexOf(" ON ")>0){
                String beforeSql = sql.substring(0,upperSql.indexOf(" ON ")+4);
                String afterSql = sql.substring(upperSql.indexOf(" ON ")+4);
                log.info("beforeSql:{}",beforeSql);
                log.info("afterSql:{}",afterSql);
                //匹配On关键字前面表的别名
                String alias = getBeforeOnAlias(sql);
                if(upperSql.indexOf("WHERE")>0){
                    return whereSQL(sql, tenantIDSql, upperSql);
                }
                return beforeSql +  alias.replace("(","")+"."+tenantIDSql +" and " +afterSql +"  ";
            }else if(upperSql.indexOf("ORDER BY")>0){
                return orderSQL(sql, tenantIDSql, upperSql);
            }
            else if(upperSql.indexOf("WHERE")>0){
                return whereSQL(sql, tenantIDSql, upperSql);
            }else{
                return sql + " where " + tenantIDSql;
            }
        }

    }

    @NotNull
    private String whereSQL(String sql, String tenantIDSql, String upperSql) {
        String beforeSql = sql.substring(0, upperSql.indexOf("WHERE")+5);
        String afterSql = sql.substring(upperSql.indexOf("WHERE")+5);
        return beforeSql + " " + tenantIDSql +" and " +afterSql +"  ";
    }

    @NotNull
    private String orderSQL(String sql, String tenantIDSql, String upperSql) {
        String beforeSql = sql.substring(0, upperSql.indexOf("ORDER BY"));
        String afterSql = sql.substring(upperSql.indexOf("ORDER BY"));
        if(beforeSql.toUpperCase().indexOf(" AND ")>0){
            return beforeSql + " and " + tenantIDSql +" "  +afterSql +"  ";
        }
        if(beforeSql.toUpperCase().indexOf("WHERE")>0){
            return beforeSql + " and " + tenantIDSql +" " +afterSql +"  ";
        }
        return beforeSql + " where " + tenantIDSql +" " +afterSql +"  ";
    }

    private String getBeforeOnAlias(String sql){
        String afterSql = sql.substring(sql.toUpperCase().indexOf(" ON ")+4);
        String alias = afterSql.substring(0,afterSql.indexOf("."));
        return alias;
    }
    public static void main(String[] args) throws JSQLParserException {
        String sql = "valid=1  AND";

        System.out.println(sql.lastIndexOf("AND"));
    }

    public static List<String> getConditions(String sql) {
        // 匹配 where 子句
        String newSql = sql.replaceAll("\\n"," ").replaceAll("`","");
        Pattern pattern = Pattern.compile("\\s+WHERE\\s+(.+)$", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(newSql);
        String whereClause = "";
        if (matcher.find()) {
            whereClause = matcher.group(1);
        }

        // 匹配所有的条件
        pattern = Pattern.compile("(.+?)\\s*(=|<>|!=|>|<|>=|<=|LIKE|IN|NOT IN|BETWEEN|NOT BETWEEN)\\s*(.+?)($|\\s+(AND|OR)\\s+)", Pattern.CASE_INSENSITIVE);
        matcher = pattern.matcher(whereClause);
        List<String> conditions = new ArrayList<>();
        int index = 0;
        while (matcher.find()) {
            String condition = matcher.group().trim();
            System.out.println("condition:" + condition);
            condition = condition.replaceAll("\\?","#").replaceAll("\\(","@L").replaceAll("\\)","@R");
            conditions.add(condition);
        }
        return conditions;
    }

    /**
     * 过虑不需要处理的表
     * @param sql
     */
    private boolean handleFilterTable(String sql){
        String[] tables=new String[]{
          "act_","qrtz_","t_sys_captcha","t_sys_tenant_info","t_course_conflict"
        };
        for (String table : tables) {
            if(sql.indexOf(table)>0){//过虑以tables中指定字符开头的表
                return false;
            }
        }
        return true;
    }

    /**
     * 需要被处理的表
     * @param sql
     */
    private boolean appendSqlTable(String sql){
        String[] tables=new String[]{
                "t_subject"
        };
        for (String table : tables) {
            if(sql.indexOf(table)>0){
                return true;
            }
        }
        return false;
    }




}
